package com.classes;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Timestamp;
import java.sql.Blob;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.ArrayList;
import java.util.TreeMap;

import org.json.simple.*;
import com.mbbmap.app.dao.UserDao;
import com.mbbmap.app.home.SecGroupHome;
import com.mbbmap.app.home.SecUsersHome;
import com.mbbmap.app.manager.ConfigManager;
import com.mbbmap.app.manager.DBConnectionManager;
import com.mbbmap.security.dao.SecGroupsDao;
import com.mbbmap.util.Constants;

public class Casemast {
	private long id = 0;
	private String group_code = "";
	private String group_name = "";
	private String type = "";
	private String action = "";
	private String status = "";
	private String action2 = "";
	private String status2 = "";
	private String branch2 = "";
	private String acquirer = "";
	private String reference_no = "";
	private String last_reference_no = "";
	private String country_code = "";
	private String document_checklist = "";
	private Merchant merchant;
	private long created_date;
	private String created_by;
	private long submit_date;
	private String external_remark = "";
	private String external_ref_no = "";
	private String external_code = "";
	private String exported_external_code = "";
	private String reseller_location = "";
	private ArrayList<TreeMap<String, String>> histories;
	private ArrayList<TreeMap<String, String>> histories2;
	private ArrayList<TreeMap<String, String>> documents;
	private ArrayList<CaseOutlet> outlets;
	private JSONArray recommendations;
	
	public static String formatCreatedDate(String fmt, long created_dt){
		SimpleDateFormat df = new SimpleDateFormat(fmt);
		Date dt = new Date(created_dt);
		
		return df.format(dt);
	}
	
	public long getId() {
		return this.id;
	}
	
	public String getGroupCode() {
		return this.group_code;
	}
	
	public String getGroupName() {
		return this.group_name;
	}
	
	public String getType() {
		return this.type;
	}
	
	public String getAction() {
		return this.action;
	}
	
	public String getStatus() {
		return this.status;
	}
	
	public String getAction2() {
		return this.action2;
	}
	
	public String getStatus2() {
		return this.status2;
	}
	
	public String getBranch2() {
		return this.branch2;
	}
	
	public String getAcquirer() {
		return this.acquirer;
	}
	
	public Merchant getMerchant() {
		return this.merchant;
	}
	
	public long getCreatedDate(){
		return this.created_date;
	}
	
	public long getSubmitDate(){
		return this.submit_date;
	}
	
	public String getCreatedBy() {
		return this.created_by;
	}
	
	public String getReferenceNo() {
		return this.reference_no;
	}
	
	public String getLastReferenceNo() {
		return this.last_reference_no;
	}
	
	public String getCountryCode() {
		return this.country_code;
	}
	
	public String getDocumentChecklist() {
		return this.document_checklist;
	}
	
	public String getExternalRemark() {
		return this.external_remark;
	}
	
	public String getExternalRefNo() {
		return this.external_ref_no;
	}
	
	public String getExternalCode() {
		return this.external_code;
	}
	
	public String getExportedExternalCode() {
		return this.exported_external_code;
	}
	
	public String getResellerLocation() {
		return this.reseller_location;
	}
	
	public ArrayList<CaseOutlet> getOutlets(){
		return this.outlets;
	}
	
	public ArrayList<TreeMap<String, String>> getHistories(){
		return this.histories;
	}
	
	public ArrayList<TreeMap<String, String>> getHistories2(){
		return this.histories2;
	}
	
	public ArrayList<TreeMap<String, String>> getDocuments(){
		return this.documents;
	}
	
	public JSONArray getRecommendations(){
		return this.recommendations;
	}
	
	public TreeMap<String, String> getDocument(long doc_id){

        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);

        PreparedStatement statement = null;
		try{
			String sql = "select cd_id, cd_name, cd_type, cd_length, cd_created_by, cd_created_date "
				+ "from casedoc where cd_id = ?";
			
			statement = conn.prepareStatement(sql);
			statement.setLong(1, doc_id);
			
			ResultSet resultSet = statement.executeQuery();
			
			if(resultSet.next()){
				TreeMap<String, String> docLog = new TreeMap<String, String>();
				docLog.put("id", resultSet.getString("cd_id"));
				docLog.put("name", resultSet.getString("cd_name"));
				docLog.put("type", resultSet.getString("cd_type"));
				docLog.put("length", resultSet.getString("cd_length"));
				docLog.put("created_by", resultSet.getString("cd_created_by"));
				docLog.put("created_date", Long.toString(((Timestamp) resultSet.getTimestamp("cd_created_date")).getTime()));
				
				return docLog;
			}
			
			
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try { statement.close(); } catch (Exception e) {}
			}
			connMgr.freeConnection(connectionPool, conn);
		}
		
		return null;
	}
	
	public byte[] getDocumentBytes(long doc_id){
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        PreparedStatement statement = null;
		try{
			String sql = "select cd_data from casedoc where cd_id = ?";
			
			statement = conn.prepareStatement(sql);
			statement.setLong(1, doc_id);
			
			ResultSet resultSet = statement.executeQuery();
			
			if(resultSet.next()){
				Blob b = resultSet.getBlob("cd_data");
				byte[] data = b.getBytes(1, (int) b.length());
				
				return data;
			}
			
			
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try { statement.close(); } catch (Exception e) {}
			}
			connMgr.freeConnection(connectionPool, conn);
		}
		
		return null;
	}
	
	public void loadOutlets(){
		this.loadOutlets(this.id);
	}
	
	public void loadOutlets(long case_id){
		
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        PreparedStatement statement = null;
		try{
			String sql = "select * from caseoutlet where co_case_id = ? order by co_outlet_id asc";
			
			statement = conn.prepareStatement(sql);
			statement.setLong(1, case_id);
			
			ResultSet resultSet = statement.executeQuery();
			
			this.outlets = new ArrayList<CaseOutlet>();
			
			while(resultSet.next()){
				JSONObject obj = (JSONObject)JSONValue.parse(resultSet.getString("co_outlet_details"));
				
				Merchant m = new Merchant();
				m.setId(Long.parseLong(obj.get("merchant_id").toString()));
				m.setRegisteredName(obj.get("registered_name").toString());
				m.setRegisteredNo(obj.get("registered_no").toString());
				
				Outlet o = new Outlet();
				o.setId(Long.parseLong(resultSet.getString("co_outlet_id")));
				o.setName(obj.get("trading_name").toString());
				o.setLicenseNo(obj.get("license_no").toString());
				o.setLicenseType(obj.get("license_type").toString());
				o.setLicenseIssuer(obj.get("issuer").toString());
				o.setLicenseState(obj.get("state").toString());
				o.setLicenseExpiry(obj.get("expiry_date").toString());
				o.setRiskLevel(obj.get("risk_level").toString());
				o.setCTName(obj.get("contact_person").toString());
				o.setCTDesignation(obj.get("designation").toString());
				o.setCTEmail(obj.get("email").toString());
				o.setCTTel(obj.get("telephone_no").toString());
				o.setCTFax(obj.get("fax_no").toString());
				o.setPaymentOpt(resultSet.getString("co_pymt_option"));
				o.setStatementOpt(resultSet.getString("co_stmt_option"));
				o.setOtherInfo(resultSet.getString("co_other_info"));
				o.setAddress(resultSet.getString("co_address"));
				o.setAddress2(resultSet.getString("co_address_2"));
				o.setAddress3(resultSet.getString("co_address_3"));
				o.setMDRRate(resultSet.getString("co_mdr_rate"));
				
				o.setMerchant(m);
				
				
				CaseOutlet a = new CaseOutlet();
				a.setOutletDtl(resultSet.getString("co_outlet_details"));
				a.setESARefNo(resultSet.getString("co_esa_ref_no"));
				a.setNumTerminal(resultSet.getString("co_num_terminal"));
				a.setTrmRental(resultSet.getString("co_trm_rental"));
				a.setTrmOwner(resultSet.getString("co_trm_owner"));
				a.setTrmDepositWired(resultSet.getString("co_trm_deposit_wired"));
				a.setTrmDepositWireless(resultSet.getString("co_trm_deposit_wireless"));
				a.setTrmRentalDepositWired(resultSet.getString("co_trm_rental_deposit_wired"));
				a.setTrmRentalDepositWireless(resultSet.getString("co_trm_rental_deposit_wireless"));
				a.setTrmRentalAdvance(resultSet.getString("co_trm_rental_advance"));
				a.setChequeAmt(resultSet.getString("co_cheque_amt"));
				a.setChequeIssuer(resultSet.getString("co_cheque_issuer"));
				a.setChequeNo(resultSet.getString("co_cheque_no"));
				a.setChequeDate(resultSet.getString("co_cheque_date"));
				a.setRecommendation(resultSet.getString("co_recommendation"));
				
				a.setOutlet(o);
				
				this.outlets.add(a);
			}
			
			
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			if (statement != null) {
				try { statement.close(); } catch (Exception e) {}
			}
			connMgr.freeConnection(connectionPool, conn);
		}
	}
	
	public void loadHistories(int branch){
		
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);

		try{
			String group_regex = "ug::";
			String user_regex = "uu::";
			
			String sql = "select casehist.*, tbluser.name "
					+ "from casehist "
					+ "left join tbluser on ch_created_by = tbluser.login_id "
					+ "where ch_case_id = ? "
					+ "and ch_branch = ? "
					+ "order by 1 desc";
			
			PreparedStatement statement = conn.prepareStatement(sql);
			
			try {
				statement.setLong(1, this.id);
				statement.setInt(2, branch);
				
				ResultSet resultSet = statement.executeQuery();
				
				if(branch == 1){
					this.histories = new ArrayList<TreeMap<String, String>>();
				} else {
					this.histories2 = new ArrayList<TreeMap<String, String>>();
				}
				
				try {
					while(resultSet.next()){
						TreeMap<String, String> histLog = new TreeMap<String, String>();
						histLog.put("id", resultSet.getString("ch_id"));
						histLog.put("action", resultSet.getString("ch_action"));
						histLog.put("status", resultSet.getString("ch_status"));
						histLog.put("last_status", resultSet.getString("ch_last_status"));
						
						String temp = resultSet.getString("ch_note");
						if(resultSet.wasNull()){
							temp = "";
						}
						
						histLog.put("note", temp);
						
						histLog.put("assigned_to", resultSet.getString("ch_assigned_to"));
						histLog.put("created_by", resultSet.getString("ch_created_by"));
						
						String fv = resultSet.getString("name");
						if(resultSet.wasNull() == false){
							histLog.put("created_by", resultSet.getString("name"));
						}
						
						histLog.put("created_date", Long.toString(((Timestamp) resultSet.getTimestamp("ch_created_date")).getTime()));
						
						if(branch == 1) {
							this.histories.add(histLog);
						}
						else if(branch == 2) {
							this.histories2.add(histLog);
						}
					}
					
				} finally {
					resultSet.close();
				}
				
			} finally {
				statement.close();
			}
			
			
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}
	
	public void loadHistories(String strEMerchant, int branch){
		
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);

		try{
			String group_regex = "ug::";
			String user_regex = "uu::";
			
			String sql = "select casehist.*, tbluser.name "
					+ "from casehist "
					+ "left join tbluser on ch_created_by = tbluser.login_id "
					+ "where ch_case_id = ? "
					+ "and ch_branch = ? "
					+ "order by 1 desc";
			
			PreparedStatement statement = conn.prepareStatement(sql);
			
			try {
				statement.setLong(1, this.id);
				statement.setInt(2, branch);
				
				ResultSet resultSet = statement.executeQuery();
				
				if(branch == 1){
					this.histories = new ArrayList<TreeMap<String, String>>();
				} else {
					this.histories2 = new ArrayList<TreeMap<String, String>>();
				}
				
				ArrayList<SecGroupsDao> arlSecGroupList = (ArrayList<SecGroupsDao>) SecGroupHome.getInstance().findSecGroupList(strEMerchant);
				
				try {
					while(resultSet.next()){
						TreeMap<String, String> histLog = new TreeMap<String, String>();
						histLog.put("id", resultSet.getString("ch_id"));
						histLog.put("action", resultSet.getString("ch_action"));
						histLog.put("status", resultSet.getString("ch_status"));
						histLog.put("last_status", resultSet.getString("ch_last_status"));
						
						String temp = resultSet.getString("ch_note");
						if(resultSet.wasNull()){
							temp = "";
						}
						
						histLog.put("note", temp);
						
						temp = resultSet.getString("ch_assigned_to");
						if(resultSet.wasNull() == true){
							temp = "";
						}
						else {
							JSONArray arr = (JSONArray) JSONValue.parse(temp);
							
							String sep = "";
							temp = "";
							
							for(Object s : arr){
								String s_item = s.toString();
								
								if(s_item.startsWith(user_regex)) {
									String[] a = s_item.split(user_regex);
									String userId = a[1];
									
									UserDao userdao = SecUsersHome.getInstance().getUser(strEMerchant, userId);
									s_item = userdao.getUserName();
									
								} else if(s_item.startsWith(group_regex)) {
									String[] a = s_item.split(group_regex);
									String group_code = a[1];
									
									s_item = "[" + group_code + "]";
									
									for(SecGroupsDao ug : arlSecGroupList){
										if(ug.getGroupCode().equals(group_code)){
											s_item = "[" + ug.getGroupName() + "]";
										}
									}
									
								}
								
								temp += sep + s_item;
								sep = ", ";
							}
						}
						
						histLog.put("assigned_to", temp);
						histLog.put("created_by", resultSet.getString("ch_created_by"));
						
						String fv = resultSet.getString("name");
						if(resultSet.wasNull() == false){
							histLog.put("created_by", resultSet.getString("name"));
						}
						
						histLog.put("created_date", Long.toString(((Timestamp) resultSet.getTimestamp("ch_created_date")).getTime()));
						
						if(branch == 1) {
							this.histories.add(histLog);
						}
						else if(branch == 2) {
							this.histories2.add(histLog);
						}
					}
					
				} finally {
					resultSet.close();
				}
				
			} finally {
				statement.close();
			}
			
			
		} catch(Exception e){
			e.printStackTrace();
		}
	}
	
	public void loadDocuments(){
		
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        Connection conn = connMgr.getConnection(connectionPool, 10000);
        
		try{
			String sql = "select cd_id, cd_name, cd_type, cd_length, cd_ref_no, cd_created_by, cd_created_date "
				+ "from casedoc where cd_case_id = ? order by 1 desc";
			
			PreparedStatement statement = conn.prepareStatement(sql);
			
			try {
				statement.setLong(1, this.id);
				
				ResultSet resultSet = statement.executeQuery();
				
				this.documents = new ArrayList<TreeMap<String, String>>();
				
				try {
					while(resultSet.next()){
						TreeMap<String, String> docLog = new TreeMap<String, String>();
						docLog.put("id", resultSet.getString("cd_id"));
						docLog.put("name", resultSet.getString("cd_name"));
						docLog.put("type", resultSet.getString("cd_type"));
						docLog.put("length", resultSet.getString("cd_length"));
						docLog.put("ref_no", resultSet.getString("cd_ref_no"));
						docLog.put("created_by", resultSet.getString("cd_created_by"));
						docLog.put("created_date", Long.toString(((Timestamp) resultSet.getTimestamp("cd_created_date")).getTime()));
						
						this.documents.add(docLog);
					}
				} finally {
					resultSet.close();
				}
			} finally {
				statement.close();
			}
			
			
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}
	
	public void loadRecommendations(String mid_pos, String mid_amex){
        int TIMEOUT = 10000; //DB connection timeout if unable to get connection in less than 10 seconds
        String connectionPool = ConfigManager.getInstance().getConnectionPool();
        DBConnectionManager connMgr = DBConnectionManager.getInstance();
        try { TIMEOUT = Integer.parseInt(ConfigManager.getInstance().get(Constants.DB_WAIT_TIME)); } catch (Exception e) {}
        Connection conn = connMgr.getConnection(connectionPool, TIMEOUT);
		try{
			String sql = "select co_recommendation from caseoutlet ";
			
			if(mid_pos.length() > 0) {
				sql += "where co_mid_pos = ? ";
				
				if(mid_amex.length() > 0) {
					sql += "or co_mid_amex = ? ";
				}
			}
			else if(mid_amex.length() > 0) {
				sql += "where co_mid_amex = ? ";
			}
			
			
			PreparedStatement statement = conn.prepareStatement(sql);
			
			try {
				if(mid_pos.length() > 0) {
					statement.setString(1, mid_pos);
					
					if(mid_amex.length() > 0) {
						statement.setString(2, mid_amex);
					}
				}
				else if(mid_amex.length() > 0) {
					statement.setString(1, mid_amex);
				}
				
				
				ResultSet resultSet = statement.executeQuery();
				
				this.recommendations = new JSONArray();
				
				try {
					while(resultSet.next()){
						JSONObject obj = (JSONObject) JSONValue.parse(resultSet.getString("co_recommendation"));
						this.recommendations.add(obj);
					}
				} finally {
					resultSet.close();
				}
			} finally {
				statement.close();
			}
			
		} catch(Exception e){
			e.printStackTrace();
		} finally {
			connMgr.freeConnection(connectionPool, conn);
		}
	}
	
	public void setId(long v) {
		this.id = v;
	}
	
	public void setGroupCode(String v) {
		if(v != null)
			this.group_code = v;
	}
	
	public void setGroupName(String v) {
		if(v != null)
			this.group_name = v;
	}
	
	public void setType(String v) {
		if(v != null)
			this.type = v;
	}
	
	public void setAction(String v) {
		if(v != null)
			this.action = v;
	}
	
	public void setStatus(String v) {
		if(v != null)
			this.status = v;
	}
	
	public void setAction2(String v) {
		if(v != null)
			this.action2 = v;
	}
	
	public void setStatus2(String v) {
		if(v != null)
			this.status2 = v;
	}
	
	public void setBranch2(String v) {
		if(v != null)
			this.branch2 = v;
	}
	
	public void setAcquirer(String v) {
		if(v != null)
			this.acquirer = v;
	}
	
	public void setMerchant(Merchant v) {
		this.merchant = v;
	}
	
	public void setCreatedDate(long v){
		this.created_date = v;;
	}
	
	public void setCreatedBy(String v) {
		if(v != null)
			this.created_by = v;
	}
	
	public void setSubmitDate(long v){
		this.submit_date = v;;
	}
	
	public void setReferenceNo(String v) {
		if(v != null)
			this.reference_no = v;
	}
	
	public void setLastReferenceNo(String v) {
		if(v != null)
			this.last_reference_no = v;
	}
	
	public void setCountryCode(String v) {
		if(v != null)
			this.country_code = v;
	}
	
	public void setDocumentChecklist(String v) {
		if(v != null)
			this.document_checklist = v;
	}
	
	public void setExternalRemark(String v) {
		if(v != null) 
			this.external_remark = v;
	}
	
	public void setExternalRefNo(String v) {
		if(v != null) 
			this.external_ref_no = v;
	}
	
	public void setExternalCode(String v) {
		if(v != null) 
			this.external_code = v;
	}
	
	public void setExportedExternalCode(String v) {
		if(v != null) 
			this.exported_external_code = v;
	}
	
	public void setResellerLocation(String v) {
		if(v != null) 
			this.reseller_location = v;
	}
}